﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;

namespace BeerManagementSystem
{
    internal class SQLConnector
    {
        private LinqToBeerDataContext _linq;

        private Transaction _lastTransaction;

        public SQLConnector()
        {
            _linq = new LinqToBeerDataContext();
            UpdateLastTransaction();

        }

        private void UpdateLastTransaction()
        {
            var lastTransactionResult =
                from t in _linq.Transaction
                orderby t.Timestamp descending
                select t;

            _lastTransaction = lastTransactionResult.First();
        }

        /// <summary>
        /// Setzt die heute getrunkenen biere in der MSSQL-DB auf 0
        /// </summary>
        internal void ResetBeerDay()
        {
            foreach (User u in _linq.User)
                u.Beer_day = 0;
            _linq.SubmitChanges();
        }

        /// <summary>
        /// Setzt die diesen Monat getrunkenen Biere in der MSSQL-Datenbank auf 0
        /// </summary>
        internal void ResetBeerMonth()
        {
            foreach (User u in _linq.User)
                u.Beer_month = 0;
            _linq.SubmitChanges();
        }

        internal DateTime GetLastTransactionDateTime()
        {
            return _lastTransaction.Timestamp;
        }

        internal User GetUser(int cardId)
        {
            var userResult =
                from cards in _linq.Card
                where cards.ID == cardId
                select cards.User;
            if (!userResult.Any())
            {
                return null;
            }
            return userResult.First();
        }

        internal User GetUser(string fName, string lName)
        {
            var userResult =
                from user in _linq.User
                where (user.FirstName == fName && user.LastName == lName)
                select user;

            if (!userResult.Any())
            {
                return null;
            }

            return userResult.First();
        }

        internal User GetUserById( int id)
        {
            var userResult =
                from user in _linq.User
                where (user.ID == id)
                select user;

            if (!userResult.Any())
            {
                return null;
            }

            return userResult.First();
        }

        internal Transaction GetLastTransaction(User user)
        {
            var userTransactions =
                from t in _linq.Transaction
                where t.User == user
                orderby t.Timestamp descending
                select t;
            try
            {
                return userTransactions.First();
            }
            catch (Exception)
            {
                return null;
            }

        }

        internal void WriteTransaction(User currentUser, byte transactionType, Drink drink)
        {
            var ta = new Transaction
                {
                    User = currentUser,
                    Timestamp = DateTime.Now,
                    Type = transactionType,
                    Drink_Type = (byte) drink
                };
            _linq.Transaction.InsertOnSubmit(ta);
            _linq.SubmitChanges();

            LogWriter.Write("T: " + ta.ToString(),LogWriter.LogLevel.Status, this);
            UpdateLastTransaction();
        }

        internal void AddToAccountOther(User currentUser, double p)
        {
            currentUser.Account_other += (decimal) p;
            _linq.SubmitChanges();
        }

        internal void IncrementUserBeer(User currentUser)
        {
            currentUser.Beer_total++;
            currentUser.Beer_month++;
            currentUser.Beer_day++;
            _linq.SubmitChanges();
        }

        internal void DecrementUserBeer(User currentUser)
        {
            if (currentUser.Beer_total > 0) currentUser.Beer_total--;
            if (currentUser.Beer_month > 0) currentUser.Beer_month--;
            if (currentUser.Beer_day > 0) currentUser.Beer_day--;
            _linq.SubmitChanges();
        }

        internal DateTime GetLastTransactionDateTime(User currentUser)
        {
            if (GetLastTransaction(currentUser) == null) return DateTime.Now;
            return GetLastTransaction(currentUser).Timestamp;
        }

        internal void IncreaseDuration(User user, decimal p)
        {
            user.Duration_total += p;
            _linq.SubmitChanges();
        }

        internal bool IsLiquid(User currentUser, decimal limit)
        {
            if (currentUser.Account_beerbill == null) return true;
            return currentUser.Account_beerbill + limit > 0;
        }

        internal User[] GetRankingTotal(int count)
        {
            return _linq.User.OrderByDescending(u => u.Beer_total).Take(count).ToArray();
        }

        internal User[] GetRankingMonth(int count)
        {
            return
                _linq.User.OrderByDescending(u => u.Beer_month)
                     .AsEnumerable()
                     .TakeWhile(c => c.Beer_month > 0)
                     .Take(count)
                     .ToArray();
        }

        internal User[] GetRankingDay(int count)
        {
            return
                _linq.User.OrderByDescending(u => u.Beer_day)
                     .AsEnumerable()
                     .TakeWhile(c => c.Beer_day > 0)
                     .Take(count)
                     .ToArray();
        }




        internal int GetTotalBeerCount()
        {
            int a = 0;
            try
            {
                a = _linq.User.Select(u => Convert.ToInt32(u.Beer_total)).Sum();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.StackTrace);
            }
            return a;
        }

        public int GetMonthBeerCount()
        {
            return Enumerable.Cast<int>(_linq.User.Select(u => u.Beer_month)).Sum();
        }

        public int GetDayBeerCount()
        {
            return Enumerable.Cast<int>(_linq.User.Select(u => u.Beer_day)).Sum();
        }

        internal Transaction GetLastTransaction()
        {
            return _lastTransaction;
        }

        public int GetUserRank(User currentUser)
        {

            var ranking = _linq.User.OrderByDescending(u => u.Beer_total)
                               .Select(u => u);
            int i = 0;
            foreach (User u in ranking)
            {
                i++;
                if (u == currentUser) return i;
            }
            return -1;
        }

        public Beerdocter GetBeerdocter(int beerdocterNumber)
        {
            var bd = new Beerdocter();
            try
            {
                var docter = from d in _linq.Beerdocter
                             orderby d.ID descending
                             select d;

                bd = docter.Skip(beerdocterNumber).First();
            }
            catch (Exception e)
            {
                LogWriter.WriteMessage("Beerdocter: Update Current Docter " + e.StackTrace);

            }
            return bd;
        }

        public int GetBeerDocterCount()
        {
            return _linq.Beerdocter.Count();
        }

        public void SetUserBeerbillValue(User user, decimal dDecimal)
        {
            user.Account_beerbill = dDecimal;
#if DEBUG
            LogWriter.WriteMessage("SetUserBeerbillValue: " + user.FirstName + "/" + user.LastName + "/" +
                                   dDecimal.ToString());
#endif
        }

        public void SubmitChanges()
        {
            _linq.SubmitChanges();
        }

        public void UserExists(string toString, string s)
        {
            throw new NotImplementedException();
        }

        internal int DeleteLastTransaction()
        {
            int i = Convert.ToInt32(_lastTransaction.Type);
            LogWriter.WriteMessage("Delete: Transaction: " + _lastTransaction.ToString());
            _linq.Transaction.DeleteOnSubmit(_lastTransaction);
            _linq.SubmitChanges();
            UpdateLastTransaction();
            return i;
        }

        public string[] GetUserNames()
        {
            return _linq.User
                        .OrderBy(u => u.FirstName)
                        .OrderBy(u => u.LastName)
                        .Select(u => u.FirstName + " " + u.LastName)
                        .ToArray();
        }

        public void SetUserToCard(User user, int cardId)
        {
            if (_linq.Card.Where(c => c.ID == cardId).Select(c => c).Any())
            {
                Card userCard = _linq.Card.Where(c => c.ID == cardId).Select(c => c).First();
                userCard.UserID = user.ID;
                _linq.SubmitChanges();
            }
            else
            {
                Card c = new Card
                    {
                        ID = cardId,
                        UserID = user.ID,
                        Type = 1
                    };
                _linq.Card.InsertOnSubmit(c);
                _linq.SubmitChanges();
                LogWriter.Write("Karte zugewiesen" + c.ID +" " + c.UserID, LogWriter.LogLevel.Status, this);
            }
        }

        public void CreateUser(string pre, string last)
        {
            User user = new User
                {
                    FirstName = pre,
                    LastName = last,
                    Duration_total = 0,
                    Beer_total = 0,
                    Beer_day = 0,
                    Beer_month = 0,
                    Account_beerbill = 0,
                    Account_game = 0,
                    Account_other = 0,
                    LastTransaction_Datetime = DateTime.Now
                };
            _linq.User.InsertOnSubmit(user);
            _linq.SubmitChanges();
            
        }


        public List<User> GetAllUsers()
        {
            List<User> userList = new List<User>();
            foreach (var user in _linq.User)
            {
                userList.Add(user);
            }
            return userList;
        }

        public void WriteCashTransaction(User cashUser, decimal dec, string comment)
        {
            decimal accountAlt = Convert.ToDecimal(cashUser.Account_beerbill);
            CashTransaction ct = new CashTransaction
                {
                    UserID = cashUser.ID,
                    Value = Convert.ToDecimal(dec),
                    Timestamp = DateTime.Now,
                    Account = accountAlt + Convert.ToDecimal(dec),
                    Comment = comment
                };
            _linq.CashTransaction.InsertOnSubmit(ct);
            cashUser.Account_beerbill += dec;

            LogWriter.WriteMessage("CashTransaction: " +
                                   String.Format(
                                       "{0} / {1} / {2} /  / Account_alt: {3} / Value: {4} / Account_neu: {5} // Comment: {6}"
                                       , ct.ID, ct.UserID, ct.Timestamp, accountAlt, ct.Value, cashUser.Account_beerbill,
                                       ct.Comment));
            _linq.SubmitChanges();
            
        }

        private decimal GetBeerbillAccount(int id)
        {
            var getBeerbillAccount = from u in _linq.User
                                     where u.ID == id
                                     select u.Account_beerbill;
            if (getBeerbillAccount.Any()) return Convert.ToDecimal(getBeerbillAccount.First());
            return 0.0m;
        }

        internal void UpdateBeerbillAccount(DateTime date)
        {
             
            if (_linq.CashTransaction.Count(t => t.Timestamp.Year == date.Year && t.Timestamp.Month == date.Month) != 0)
            {
                DialogResult dialogResult = MessageBox.Show("Beerbill für den Monat schon berechnet. Date:" + date.ToLongDateString() + "trotzdem berechnen?", "Beerbill bereits erstellt", MessageBoxButtons.YesNo);
                LogWriter.Write(
                    "Beerbill wurde für den Monat schon berechnet und aktuallisiert. Date: " + date.ToLongDateString(),
                    LogWriter.LogLevel.Warning, this);
                if (dialogResult == DialogResult.No) return;
            }

            MessageBox.Show("calc");
            foreach (var user in _linq.User)
            {
                
                if (user.Account_beerbill == null) user.Account_beerbill = 0.0m;
                decimal cashTransaction = 0;
                var transactions =
                    _linq.Transaction.Where(t => t.User == user)
                         .Where(t => t.Type != 2)
                         .Where(t => t.Timestamp.Year == date.Year && t.Timestamp.Month == date.Month)
                         .Select(t => t);
                foreach (var result in transactions)
                {
                    switch (result.Drink_Type)
                    {
                        case 1:
                            cashTransaction -= Properties.Settings.Default.PriceBeer;
                            break;
                        case 2:
                            cashTransaction -= Properties.Settings.Default.PriceWine;
                            break;
                        case 3:
                            cashTransaction -= Properties.Settings.Default.PriceShot;
                            break;
                    }
                }

                _linq.SubmitChanges();
                WriteCashTransaction(user, cashTransaction, "Beerbill" + date.Month + "/"+ date.Year);
            }
            LogWriter.Write("Beerbill wurde für " + date.ToLongDateString() + " erstellt.",
                                LogWriter.LogLevel.Status, this);
        }

        internal void MissMatch()
        {

            foreach (User u in _linq.User)
            {
                var userWineTransactions = from t in u.Transaction
                                           where
                                               ((t.Timestamp.CompareTo(new DateTime(DateTime.Now.Year,
                                                                                    DateTime.Now.Month, 1, 0, 0, 0)
                                                                           .AddMonths(-2)) > 0) &&
                                                
                                                (t.Type != 2) && (t.Drink_Type == 2))

                                           select t;
                var userShotTransactions = from t in u.Transaction
                                           where
                                               ((t.Timestamp.CompareTo(new DateTime(DateTime.Now.Year,
                                                                                    DateTime.Now.Month, 1, 0, 0, 0)
                                                                           .AddMonths(-2)) > 0) 
                                                && (t.Type != 2) && (t.Drink_Type == 3))
                                           select t;
                var userBeerTransactions = from t in u.Transaction
                                           where
                                               ((t.Timestamp.CompareTo(new DateTime(DateTime.Now.Year,
                                                                                    DateTime.Now.Month, 1, 0, 0, 0)
                                                                           .AddMonths(-2)) > 0)
                                                && (t.Type != 2) && (t.Drink_Type == 1))
                                           select t;
                foreach (var userBeerTransaction in userBeerTransactions)
                {
                   Console.WriteLine(userBeerTransaction.ToString());
                }
                u.Account_other =
                    Convert.ToDecimal(userWineTransactions.Count()*1.5 + userShotTransactions.Count()*0.5);
                u.Beer_month = userBeerTransactions.Count();
                _linq.SubmitChanges();

            }

        }

        internal Dictionary<User, string> GetAllMailadresses()
        {
            Dictionary<User,string> adresses = new Dictionary<User, string>();

            foreach (var user in _linq.User)
            {
                if(user.Mail != null) adresses.Add(user, user.Mail);
            }
            return adresses;
        }

        public void test()
        {
            foreach (var U  in _linq.User)
            {
                //U.Last_WebUpdate = DateTime.Now;
            }
            _linq.SubmitChanges();
        }

        public void UpdateUser(User user)
        {
            User u = user;
            _linq.SubmitChanges();
            LogWriter.WriteStatus("User aktualisiert:@ " + user.FirstName + " " + user.Last_WebUpdate + " "+ user.Hobbies + " "
                                    + user.UserInfo);
        }

        public int GetCardType(int i)
        {
            return _linq.Card.Where(c => c.ID == i).Select(c => c.Type).First();
        }

        public void ResetGuestCards()
        {
            foreach (var card in _linq.Card)    
            {
                if (card.Type == 2) card.Type = 3;
            }
            _linq.SubmitChanges();
        }

        public void SetCardType(int type, int cardId)
        {
            
            Card card = _linq.Card.Where(c => c.ID == cardId).Select(c => c).First();
            card.Type = (byte)type;
            _linq.SubmitChanges();
            

        }

        internal void IncreaseUserGameAccount(User user, decimal p)
        {
            _linq.User.Where(u => u.ID == user.ID).Select(u => u).First().Account_game += p;
            _linq.SubmitChanges();
        }

        internal void AddToAccountBeerbill(User user, decimal p)
        {
            _linq.User.Where(u => u.ID == user.ID).Select(u => u).First().Account_beerbill += Properties.Settings.Default.PriceBeer;
            _linq.SubmitChanges();
        }

        public List<CashTransaction> GetCashTransactions(User user)
        {
            return _linq.CashTransaction.Where(ct => ct.UserID == user.ID).Select(ct => ct).OrderByDescending(ct => ct.ID).ToList();
        }

        public string GetTotalBeerbill()
        {
            MessageBox.Show(_linq.Transaction.Where(t => t.UserID == 3).Count(t => t.Type != 3).ToString());
            return _linq.User.Select(u => u).Sum(u => u.Account_beerbill).ToString();
            
        }
    }

    public static class Extensions
    {
        public static decimal GetPrice(this Drink drink)
        {
            switch (drink)
            {
                case Drink.Beer:
                    return Properties.Settings.Default.PriceBeer;
                case Drink.Shot:
                    return Properties.Settings.Default.PriceShot;
                case Drink.Wine:
                    return Properties.Settings.Default.PriceWine;
            }
            return 0;
        }
    }
}
